package news.tencent.charco.android.utils;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

import news.tencent.charco.android.NewsApplication;
import news.tencent.charco.android.mimc.NewsEsSource;

public class DbOperateUtil extends SQLiteOpenHelper {

    private static final String TAG = "DbOperateUtil";

    private static volatile DbOperateUtil DbHelper;

    public static DbOperateUtil getInstance() {
        if (DbHelper == null) {
            synchronized (DbOperateUtil.class) {
                if (DbHelper == null) {
                    try {
                        DbHelper = new DbOperateUtil(NewsApplication.getContext(), "sys.db", null, 1);
                    } catch (Exception e) {
                        Log.i(TAG, "DbOperateUtil 实例创建失败 : " + e);
                    }
                }
            }
        }
        return DbHelper;
    }

    public DbOperateUtil(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            String CREATE_BOOK = "create table IF NOT EXISTS tb_news ( id integer primary key autoincrement,\n" +
                    "nid text,\n" +
                    "indexTime integer,\n" +
                    "news_type text,\n" +
                    "src text,\n" +
                    "digest text,\n" +
                    "time text,\n" +
                    "createtime TIMESTAMP default (datetime('now', 'localtime')),\n" +
                    "UNIQUE (nid) )";
            db.execSQL(CREATE_BOOK);

            String CREATE_KW_HISTORY = "create table IF NOT EXISTS tb_keywords ( id integer primary key autoincrement,\n" +
                    "keyword text,\n" +
                    "createtime TIMESTAMP default (datetime('now', 'localtime')) )";
            db.execSQL(CREATE_KW_HISTORY);
        }catch (Exception e) {
            Log.i(TAG, "DbOperateUtil onCreate 失败 : " + e);
        }
    }

    public void deleteData3(){
        try {
            SQLiteDatabase db = DbHelper.getWritableDatabase();
            db.execSQL("DELETE FROM tb_news WHERE date('now', '-3 day') >= date(createtime)");
            db.close();// 记得关闭数据库操作
        } catch (Exception e) {
            Log.i(TAG, "deleteData3 execSQL 失败 : " + e);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void insert(NewsEsSource source) {
        try {
            SQLiteDatabase db = DbHelper.getWritableDatabase();
            db.execSQL("insert into tb_news (nid,indexTime,news_type,src,digest,time) values(?,?,?,?,?,?)", new Object[]{
                    source.getNid(), source.getIndexTime(), source.getNews_type(), source.getSrc(), source.getDigest(), source.getTime()});
            db.close();// 记得关闭数据库操作
        } catch (Exception e) {
            Log.i(TAG, "DbOperateUtil insert 失败 : " + e);
        }
    }

    public List<NewsEsSource> getAllByType(String newsType) {
        List<NewsEsSource> lists = new ArrayList<NewsEsSource>();
        SQLiteDatabase db = DbHelper.getReadableDatabase();
        // Cursor cursor=db.rawQuery("select * from t_users limit ?,?", new
        // String[]{offset.toString(),maxLength.toString()});
        // //这里支持类型MYSQL的limit分页操作

        Cursor cursor = db.rawQuery("select * from tb_news where news_type = ? ORDER by indexTime DESC limit 100", new String[]{newsType});
        while (cursor.moveToNext()) {
            NewsEsSource source = new NewsEsSource();
            source.setNid(cursor.getString(cursor.getColumnIndex("nid")));
            source.setIndexTime(cursor.getLong(cursor.getColumnIndex("indexTime")));
            source.setNews_type(cursor.getString(cursor.getColumnIndex("news_type")));
            source.setSrc(cursor.getString(cursor.getColumnIndex("src")));
            source.setDigest(cursor.getString(cursor.getColumnIndex("digest")));
            source.setTime(cursor.getString(cursor.getColumnIndex("time")));
            lists.add(source);
        }
        db.close();
        return lists;
    }

    // 删除关键字
    public void deleteKeyword(String keyword){
        try {
            SQLiteDatabase db = DbHelper.getWritableDatabase();
            db.execSQL("DELETE FROM tb_keywords WHERE keyword = ?",new String[]{keyword});
            db.close();// 记得关闭数据库操作
        } catch (Exception e) {
            Log.i(TAG, "DeleteKeyword execSQL 失败 : " + e);
        }
    }

    // 保存关键字
    public void saveKeyword(String keyword){
        try {
            SQLiteDatabase db = DbHelper.getWritableDatabase();
            db.execSQL("insert into tb_keywords (keyword) values(?)", new String[]{keyword});
            db.close();// 记得关闭数据库操作
        } catch (Exception e) {
            Log.i(TAG, "DbOperateUtil saveKeyword 失败 : " + e);
        }
    }

    public List<String> getAllKeywords() {
        List<String> lists = new ArrayList<String>();
        SQLiteDatabase db = DbHelper.getReadableDatabase();
        // Cursor cursor=db.rawQuery("select * from t_users limit ?,?", new
        // String[]{offset.toString(),maxLength.toString()});
        // //这里支持类型MYSQL的limit分页操作

        Cursor cursor = db.rawQuery("select * from tb_keywords ORDER by createtime DESC", new String[]{});
        while (cursor.moveToNext()) {
            String keyword = cursor.getString(cursor.getColumnIndex("keyword"));
            lists.add(keyword);
        }
        db.close();
        return lists;
    }
}
